﻿using System.ComponentModel.DataAnnotations.Schema;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper.SimpleDao.Entity;

namespace Dapper.SimpleDao.Factory
{
    /// <summary>
    /// SQL语句生成类
    /// </summary>
    internal static class MySqlDbBuilder
    {
        /// <summary>
        /// 添加SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static SqlParameterMap AddSqlMap<T>(T model) where T : BaseEntitiy
        {
            var sql = new StringBuilder();

            var param = string.Empty;
            var values = string.Empty;
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            var t = model.GetType();
            foreach (var filed in t.GetProperties())
            {
                var attrs = filed.GetCustomAttributes(typeof(KeyAttribute), false);
                var nofileds = filed.GetCustomAttributes(typeof(Nofiledable), false);
                if (filed.Name != "Id" || attrs.Length < 1 || nofileds.Length < 1)
                {
                    values += filed.Name + ",";
                    param += "@" + filed.Name + ",";
                }

            }
            sql.AppendFormat("INSERT INTO {0}({1})VALUES({2});", table, values.Trim(','), param.Trim(','));
            return new SqlParameterMap { Sql = sql.ToString(), Param = model, Operation = SqlParameterOp.Add };
        }

        /// <summary>
        /// 更新SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static SqlParameterMap UpdateSqlMap<T>(T model) where T : BaseEntitiy
        {
            var param = string.Empty;
            var values = string.Empty;
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            IDictionary<string, object> parameters = new Dictionary<string, object>();
            IDictionary<string, object> where = new Dictionary<string, object>();
            foreach (var filed in model.GetType().GetProperties())
            {
                var attrs = filed.GetCustomAttributes(typeof(KeyAttribute), false);
                if (filed.Name == "Id" || attrs.Length >= 1)
                {
                    where.Add(filed.Name, filed.GetValue(model, null));
                }
                else
                {
                    var nofileds = filed.GetCustomAttributes(typeof(Nofiledable), false);
                    if (nofileds.Length < 1)
                    {
                        parameters.Add(filed.Name, filed.GetValue(model, null));
                    }
                }
            }
            return UpdateSqlMap(table, parameters, where);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <typeparam name="TReturn">数据返回实体</typeparam>
        /// <param name="where">字段查询条件</param>
        /// <param name="page">页数</param>
        /// <param name="size">条数</param>
        /// <param name="sort">字段排序</param>
        /// <param name="op">字段查询条件拼接（1.支持全And。2.支持全Or。）</param>
        /// <returns></returns>
        public static SqlParameterMap SelectSqlMap<T, TReturn>(IEnumerable<FieldPredicate<T>> where, int page, int size, IEnumerable<PropertySort<T>> sort = null, GroupOperator op = GroupOperator.And)
            where T : BaseEntitiy
            where TReturn : class
        {
            var list = new List<string>();
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            foreach (var filed in typeof(TReturn).GetProperties())
            {
                var nofileds = filed.GetCustomAttributes(typeof(Nofiledable), false);
                if (nofileds.Length < 1)
                {
                    list.Add(filed.Name);
                }
            }
            return SelectSqlMap(table, list.ToArray(), where, page, size, sort, op);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <typeparam name="TReturn">数据返回实体</typeparam>
        /// <param name="where">SQL字段查询方式</param>
        /// <param name="page">页数</param>
        /// <param name="size">条数</param>
        /// <param name="sort">字段排序</param>
        /// <param name="op">字段查询条件拼接（1.支持全And。2.支持全Or。）</param>
        /// <returns></returns>
        public static SqlParameterMap SelectSqlMap<T, TReturn>(IDictionary<string, object> where, int page, int size, IDictionary<string, string> sort = null, GroupOperator op = GroupOperator.And)
            where T : BaseEntitiy
            where TReturn : class
        {
            var list = new List<string>();
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            foreach (var filed in typeof(TReturn).GetProperties())
            {
                  var nofileds = filed.GetCustomAttributes(typeof(Nofiledable), false);
                  if (nofileds.Length < 1)
                  {
                      list.Add(filed.Name);
                  }
            }
            return SelectSqlMap(table, list.ToArray(), where, page, size, sort);
        }

        /// <summary>
        /// Count查询
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <typeparam name="TReturn">数据返回实体</typeparam>
        /// <param name="where">字段查询条件</param>
        /// <param name="sort">字段排序</param>
        /// <param name="op">字段查询条件拼接（1.支持全And。2.支持全Or。）</param>
        /// <returns></returns>
        public static SqlParameterMap SelectCountSqlMap<T, TReturn>(IEnumerable<FieldPredicate<T>> where, IEnumerable<PropertySort<T>> sort = null, GroupOperator op = GroupOperator.And)
            where T : BaseEntitiy
            where TReturn : class
        {
            var map = new SqlParameterMap();
            map.Operation = SqlParameterOp.Select;
            var sql = new StringBuilder();
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            var alias = "t";
            sql.AppendFormat("select count(0) as total from {0} as {1} where 1=1 ", table, alias);
            if (where != null && where.Count() > 0)
            {
                IDictionary<string, object> paras = new Dictionary<string, object>();
                foreach (var p in where)
                {
                    if (p.Operator == Operator.Like)
                    {
                        sql.AppendFormat(" {3} {2}.{0} {4} '{5}{1}{5}'", p.PropertyName, p.Value, alias, op == GroupOperator.And ? "And" : "Or", PredicateHelper.GetOperatorString(p.Operator, p.Not), p.Operator == Operator.Like ? "%" : string.Empty);
                    }
                    else
                    {
                        sql.AppendFormat(" {3} {2}.{0} {4} @{1}", p.PropertyName, p.PropertyName, alias, op == GroupOperator.And ? "And" : "Or", PredicateHelper.GetOperatorString(p.Operator, p.Not));
                    }
                    if (!paras.ContainsKey(p.PropertyName))
                    {
                        paras.Add(p.PropertyName, p.Value);
                    }
                }
                if (paras.Count() > 0)
                {
                    map.Param = paras;
                }
            }
            if (sort != null && sort.Count() > 0)
            {
                sql.Append(" order by ");
                var sortValue = string.Empty;
                foreach (var p in sort)
                {
                    sortValue = string.Format(" {2}.{0} {1},", p.PropertyName, p.Ascending == true ? "asc" : "desc", alias);
                }
                sql.AppendFormat(sortValue.TrimEnd(','));
            }
            sql.Append(";");
            map.Sql = sql.ToString();
            return map;
        }

        /// <summary>
        /// Count查询
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <typeparam name="TReturn">数据返回实体</typeparam>
        /// <param name="where">字段查询条件</param>
        /// <param name="sort">字段排序</param>
        /// <returns></returns>
        public static SqlParameterMap SelectCountSqlMap<T, TReturn>(IDictionary<string, object> where, IDictionary<string, string> sort = null)
            where T : BaseEntitiy
            where TReturn : class
        {
            var map = new SqlParameterMap();
            map.Operation = SqlParameterOp.Select;
            var sql = new StringBuilder();
            var table = AttributeExtensions.GetAttributeValue(typeof(T), (TableAttribute ta) => ta.Name);
            var alias = "t";
            sql.AppendFormat("select count(0) as total from {0} as {1} where 1=1 ", table, alias);
            if (where != null && where.Count() > 0)
            {
                foreach (var p in where)
                {
                    sql.AppendFormat(" and {2}.{0}=@{1}", p.Key, p.Key, alias);
                }
                map.Param = where;
            }
            if (sort != null && sort.Count() > 0)
            {
                sql.Append(" order by ");
                var sortValue = string.Empty;
                foreach (var p in where)
                {
                    sortValue = string.Format(" {2}.{0} {1},", p.Key, p.Value, alias);
                }
                sql.AppendFormat(sortValue.TrimEnd(','));
            }
            sql.Append(";");
            map.Sql = sql.ToString();
            return map;
        }

        #region 私有方法
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="parameters">参数</param>
        /// <param name="where">条件</param>
        /// <param name="sort">排序</param>
        /// <returns></returns>
        private static SqlParameterMap UpdateSqlMap(string table, IDictionary<string, object> parameters, IDictionary<string, object> where, IDictionary<string, string> sort = null)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("Update {0} Set ", table.ToLower());
            var i = 0;
            foreach (var p in parameters)
            {
                sql.AppendFormat("{2}.{0}=@{1}", p.Key, p.Key, table);
                if (parameters.Count() - 1 > i)
                {
                    sql.Append(",");
                }
                i++;
            }
            sql.Append(" Where ");
            i = 0;
            foreach (var p in where)
            {
                sql.AppendFormat("{2}.{0}=@{1}", p.Key, p.Key, table);
                if (where.Count() - 1 > i)
                {
                    sql.Append(",");
                }
                i++;
            }
            sql.Append(";");
            return new SqlParameterMap { Sql = sql.ToString(), Param = parameters.Union(where), Operation = SqlParameterOp.Update };
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">数据实体映射数据库</typeparam>
        /// <param name="table">表名</param>
        /// <param name="keys">字段数组</param>
        /// <param name="where">字段查询条件</param>
        /// <param name="page">页数</param>
        /// <param name="size">条数</param>
        /// <param name="sort">排序</param>
        /// <param name="op">字段查询条件拼接（1.支持全And。2.支持全Or。）</param>
        /// <returns></returns>
        private static SqlParameterMap SelectSqlMap<T>(string table, string[] keys, IEnumerable<FieldPredicate<T>> where, int page, int size, IEnumerable<PropertySort<T>> sort = null, GroupOperator op = GroupOperator.And) where T : BaseEntitiy
        {
            var map = new SqlParameterMap();
            map.Operation = SqlParameterOp.Select;
            var sql = new StringBuilder();
            List<string> list = new List<string>();
            var alias = "t";
            foreach (var s in keys)
            {
                list.Add(alias + "." + s);
            }
            sql.AppendFormat("select {0} from {1} as {2} where 1=1 ", string.Join(",", list.ToArray()), table.ToLower(), alias);

            if (where != null && where.Count() > 0)
            {
                IDictionary<string, object> paras = new Dictionary<string, object>();
                foreach (var p in where)
                {
                    if (p.Operator == Operator.Like)
                    {
                        sql.AppendFormat(" {3} {2}.{0} {4} '{5}{1}{5}'", p.PropertyName, p.Value, alias, op == GroupOperator.And ? "And" : "Or", PredicateHelper.GetOperatorString(p.Operator, p.Not), p.Operator == Operator.Like ? "%" : string.Empty);
                    }
                    else
                    {
                        sql.AppendFormat(" {3} {2}.{0} {4} @{1}", p.PropertyName, p.PropertyName, alias, op == GroupOperator.And ? "And" : "Or", PredicateHelper.GetOperatorString(p.Operator, p.Not));
                    }
                    if (!paras.ContainsKey(p.PropertyName))
                    {
                        paras.Add(p.PropertyName, p.Value);
                    }
                }
                if (paras.Count() > 0)
                {
                    map.Param = paras;
                }
            }
            if (sort != null && sort.Count() > 0)
            {
                sql.Append(" order by ");
                var sortValue = string.Empty;
                foreach (var p in sort)
                {
                    sortValue = string.Format(" {2}.{0} {1},", p.PropertyName, p.Ascending == true ? "asc" : "desc", alias);
                }
                sql.AppendFormat(sortValue.TrimEnd(','));
            }
            if (page > 0 && size > 0)
            {
                sql.AppendFormat(" limit {0},{1};", (page - 1) * size, size);
            }
            map.Sql = sql.ToString();
            return map;
        }

        /// <summary>
        ///  查询
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="keys">字段数组</param>
        /// <param name="where">字段查询条件</param>
        /// <param name="page">页数</param>
        /// <param name="size">条数</param>
        /// <param name="sort">排序</param>
        /// <param name="op">字段查询条件拼接（1.支持全And。2.支持全Or。）</param>
        /// <returns></returns>
        private static SqlParameterMap SelectSqlMap(string table, string[] keys, IDictionary<string, object> where, int page, int size, IDictionary<string, string> sort = null, GroupOperator op = GroupOperator.And)
        {
            var map = new SqlParameterMap();
            map.Operation = SqlParameterOp.Select;
            var sql = new StringBuilder();
            List<string> list = new List<string>();
            var alias = "t";
            foreach (var s in keys)
            {
                list.Add(alias + "." + s);
            }
            sql.AppendFormat("select {0} from {1} as {2} where 1=1 ", string.Join(",", list.ToArray()), table.ToLower(), alias);
            if (where != null && where.Count() > 0)
            {
                foreach (var p in where)
                {
                    sql.AppendFormat(" {3} {2}.{0}=@{1}", p.Key, p.Key, alias, op == GroupOperator.And ? "And" : "Or");
                }
                map.Param = where;
            }
            if (sort != null && sort.Count() > 0)
            {
                sql.Append(" order by ");
                var sortValue = string.Empty;
                foreach (var p in where)
                {
                    sortValue = string.Format(" {2}.{0} {1},", p.Key, p.Value, alias);
                }
                sql.AppendFormat(sortValue.TrimEnd(','));
            }
            if (page > 0 && size > 0)
            {
                sql.AppendFormat(" limit {0},{1};", (page - 1) * size, size);
            }
            map.Sql = sql.ToString();
            return map;
        }
        #endregion

    }
}
